<?php

class Home_Model extends Model {
	
	function get_user_level($user_id) {
		$user_level_query = $this->db->query("
			SELECT u.user_level
			FROM users as u
			WHERE u.user_id = " . $this->db->escape($user_id) . "
			LIMIT 1
		");
		
		$user_level_results = $user_level_query->row_array();
		
		if (count($user_level_results) != 0) {
			return $user_level_results['user_level'];
		}
		
		return REGULAR_USER;
	}

	function get_latest_processes($user_id) {
		
		// Get the user level from the database
		
		$user_level = $this->get_user_level($user_id);
		
		// Check if the user has the right to see all the processes
		
		if (($user_level == PROCESS_MANAGER) || ($user_level == ADMIN)) {
			// Get all the processes
			
			$all_processes_query = $this->db->query("
				SELECT p.process_name, u.firstname, u.surname, i.process_instance_id, i.start_date, i.end_date
				FROM processes as p, users as u, process_instances as i
				
				WHERE i.process_id = p.process_id
				AND i.user_id = u.user_id
				AND i.end_date IS NOT NULL
			");
			
			return $all_processes_query->result_array();
		}
		else {
			// Get processes where the user is process owner from
			
			$process_owner_query = $this->db->query("
				SELECT p.process_name, u.firstname, u.surname, i.process_instance_id, i.start_date, i.end_date
				FROM processes as p, users as u, process_instances as i, process_owners as o
				
				WHERE i.process_id = p.process_id
				AND i.user_id = u.user_id
				AND i.end_date IS NOT NULL
				
				AND o.process_id = p.process_id
				AND o.user_id = " . $this->db->escape($user_id)
			);
			
			$process_owner_results = $process_owner_query->result_array();
			
			// Get processes where user is a participant in a stage
			
			$stage_participants_query = $this->db->query("
				SELECT p.process_name, u.firstname, u.surname, i.process_instance_id, i.start_date, i.end_date
				FROM processes as p, users as u, process_instances as i, stages as s, stage_participants as sp
				
				WHERE i.process_id = p.process_id
				AND i.user_id = u.user_id
				AND i.end_date IS NOT NULL
				
				AND s.process_id = p.process_id
				AND sp.stage_id = s.stage_id
				AND sp.user_id = " . $this->db->escape($user_id)
			);
			
			$stage_participants_results = $stage_participants_query->result_array();
			
			// Combine and return results
			
			$all_results = array_merge($process_owner_results, $stage_participants_results);
			
			return $all_results;
		}
	}
	
	function get_file($document_id) {
		$query = $this->db->query("
			SELECT d.filename, d.filecontent
			FROM documents as d
			WHERE d.document_id = " . $this->db->escape($document_id) . "
			LIMIT 1
		");
		
		return $query->row_array();
	}
	
	function get_process_details($process_instance_id) {
		$this->load->helper('filesize_helper');
		
		// Get process data
		
		$proc_query = $this->db->query("
			SELECT i.process_id
			FROM process_instances as i
			WHERE i.process_instance_id = " . $this->db->escape($process_instance_id)
		);
		$proc_result = $proc_query->row_array();
		$process_id = $proc_result['process_id'];	
		
		$process_query = $this->db->query("
			SELECT p.process_name, i.process_instance_id, u.firstname, u.surname, i.start_date, i.end_date
			FROM processes as p, users as u, process_instances as i
			WHERE i.process_id = p.process_id
			AND i.user_id = u.user_id
			AND p.process_id = " . $this->db->escape($process_id) . "
			LIMIT 1
		");
		
		$process_data = $process_query->row_array();
		
		// Get stages data
		
		if ($process_query->num_rows() != 0) {
			$stages_query = $this->db->query("
				SELECT s.stage_name, i.stage_instance_id, i.start_date, i.end_date, u.firstname, u.surname
				FROM stages as s, stage_instances as i, users as u
				WHERE s.stage_id = i.stage_id
				AND i.user_id = u.user_id
				AND i.process_instance_id = " . $this->db->escape($process_data['process_instance_id']) . "
			");
			
			$stages_data = $stages_query->result_array();
			
		// Get contributions data
			
			if ($stages_query->num_rows() != 0) {
				$stage_instance_ids_array = array();
				
				foreach ($stages_data as $stage) {
					$stage_instance_ids_array[] = "'" . $stage['stage_instance_id'] . "'";
				}
				
				$stage_instance_ids = implode(',', $stage_instance_ids_array);
				
				$contributions_query = $this->db->query("
					SELECT c.contribution_title, i.contribution_instance_id, u.firstname, u.surname, i.added_date, i.stage_instance_id
					FROM contributions as c, contribution_instances as i, users as u
					WHERE i.user_id = u.user_id
					AND i.contribution_id = c.contribution_id
					AND i.stage_instance_id IN (" . $stage_instance_ids . ")
				");
				
				$contributions_data = $contributions_query->result_array();
				
		// Get attributes data
				
				if ($contributions_query->num_rows() != 0) {
					
					$contr_instances_ids_array = array();
					
					foreach ($contributions_data as $contr) {
						$contr_instances_ids_array[] = "'" . $contr['contribution_instance_id'] . "'";
					}
				
					$contr_instance_ids = implode(',', $contr_instances_ids_array);
					
			// Get attribute inputs
					
					$attributes_inputs_query = $this->db->query("
						SELECT a.attribute_name, a.attribute_type, a.attribute_id, i.input_value, i.contribution_instance_id
						FROM attributes as a LEFT JOIN input_datas as i
						ON (a.attribute_id = i.attribute_id)
						WHERE i.contribution_instance_id IN (" . $contr_instance_ids . ")
						
					");
					
					$attributes_data = $attributes_inputs_query->result_array();
					
			// Get attribute documents
					
					$attributes_documents_query = $this->db->query("
						SELECT a.attribute_name, a.attribute_type, a.attribute_id, d.document_id, d.contribution_instance_id, d.filename, d.filesize
						FROM attributes as a LEFT JOIN documents as d
						ON (a.attribute_id = d.attribute_id)
						WHERE d.contribution_instance_id IN (" . $contr_instance_ids . ")
						
					");
					
					$attributes_data = array_merge($attributes_data, $attributes_documents_query->result_array());
				}
				
			}
		}
		
	// Process data
		
		$data = array(
			'process_name'	=> $process_data['process_name'],
			'started_by'	=> $process_data['firstname'] . ' ' . $process_data['surname'],
			'start_date'	=> $process_data['start_date'],
			'end_date'		=> $process_data['end_date']
		);
		
	// Stages data
		
		if (isset($stages_data) && count($stages_data) != 0) {
			$data['stages'] = array();
			
			foreach ($stages_data as $stage) {
				$current_stage = array(
					'name'			=> $stage['stage_name'],
					'id'			=> $stage['stage_instance_id'],
					'doneby'		=> $stage['firstname'] . ' ' . $stage['surname'],
					'start_date'	=> $stage['start_date'],
					'end_date'		=> $stage['end_date']
				);
				
	// Contributions data
				
				$all_contributions = $this->getRowsByKeyValue($contributions_data, 'stage_instance_id', $stage['stage_instance_id']);
				
				if (count($all_contributions) != 0) {
					$stage_contributions = array();
					
					foreach ($all_contributions as $contr) {
						$current_contribution = array(
							'id'			=> $contr['contribution_instance_id'],
							'title'			=> $contr['contribution_title'],
							'added_by'		=> $contr['firstname'] . ' ' . $contr['surname'],
							'added_date'	=> $contr['added_date']
						);
						
	// Attributes data
						
						$all_attributes = $this->getRowsByKeyValue($attributes_data, 'contribution_instance_id', $contr['contribution_instance_id']);
						
						if (count($all_attributes) != 0) {
							$contr_attributes = array();
							
							foreach ($all_attributes as $attr) {
								$contr_attributions[] = array(
									'type'	=> strtolower($attr['attribute_type']),
									'name'	=> $attr['attribute_name'],
									
									'value'	=> isset($attr['input_value']) ? $attr['input_value'] : '',
									
									'document_id'	=> isset($attr['document_id']) ? $attr['document_id'] : '',
									'filename'		=> isset($attr['filename']) ? $attr['filename'] : '',
									'filesize'		=> isset($attr['filesize']) ? make_filesize_readable($attr['filesize']) : ''
								);
							}
							
							$current_contribution['attributes'] = $contr_attributions;
							$stage_contributions[] = $current_contribution;
							
							unset($contr_attributions);
						}
					}
					
					$current_stage['contributions'] = $stage_contributions;
					
					unset($stage_contributions);
				}
				
				$data['stages'][] = $current_stage;
				
				unset($current_stage);
			}
		}
		
		return $data;
	}
	
	private function getRowsByKeyValue($source, $key, $value) {
		
		$res = array();
		
		foreach($source as $row) {
			if ($row[$key] == $value) {
				$res[] = $row;
			}
		}
		
		return $res;
	}
	
	function get_processes_started_by_user($user_id) {
		// Load helpers
		$this->load->helper('date2');
		
		// Get all the needed processes from the database
		
		$processes_query = $this->db->query("
			SELECT i.process_instance_id, p.process_name, i.start_date
			FROM processes as p, process_instances as i
			WHERE i.user_id = " . $this->db->escape($user_id) . "
			AND i.process_id = p.process_id
			AND i.end_date IS NULL
		");
		
		$processes_data = $processes_query->result_array();
		
		// Build a list with all the process ids
		
		if (count($processes_data) != 0) {
			$process_ids = array();
			
			foreach ($processes_data as $p) {
				$process_instance_ids[] = "'" . $p['process_instance_id'] . "'";
			}
			
			$process_instance_ids = implode(',', $process_instance_ids);
			
			// Get all the needed (unfinished) stages from the database
			
			$current_stage_query = $this->db->query("
				SELECT i.process_instance_id, s.stage_name, i.start_date
				FROM stages as s, stage_instances as i
				WHERE i.stage_id = s.stage_id
				AND i.end_date IS NULL
				AND i.process_instance_id IN (" . $process_instance_ids . ")
			");
			
			$current_stage_data = $current_stage_query->result_array();
			
			// Model all the data into 1 array
			
			$data = array();
			
			foreach ($processes_data as $p) {
				$data[$p['process_instance_id']] = array(
					'process_name'	=> $p['process_name'],
					'start_date'	=> reformat_date($p['start_date']),
					'stages'		=> array()
				);
			}
			
			foreach ($current_stage_data as $s) {
				$data[$s['process_instance_id']]['stages'][] = array(
					'stage_name'	=> $s['stage_name'],
					'start_date'	=> reformat_date($s['start_date'])
				);
			}
			
			return $data;
			
		}
	}
	
	function get_processes_owned_by_user($user_id) {
		
		// Get the needed processes from the database
	
		$query = $this->db->query("
			SELECT p.process_id, p.process_name
			FROM processes as p, process_owners as o
			WHERE p.process_id = o.process_id
			AND o.user_id = " . $this->db->escape($user_id)
		);
		
		$processes = $query->result_array();
		
		// Model the data into the right format
		
		$data = array();
		
		foreach ($processes as $p) {
			$data[$p['process_id']] = $p['process_name'];
		}
		
		return $data;
	}
	
	function get_all_processes() {
		
		// Get the processes from the database
	
		$query = $this->db->query("
			SELECT p.process_id, p.process_name
			FROM processes as p
		");
		
		$processes = $query->result_array();
		
		// Model the data into the right format
		
		$data = array();
		
		foreach ($processes as $p) {
			$data[$p['process_id']] = $p['process_name'];
		}
		
		return $data;
	}
	
	function start_process($user_id, $process_id) {
		
		// Check if the process has a first stage	
	
		$first_stage_query = $this->db->query("
			SELECT s.stage_id
			FROM stages as s
			WHERE s.stage_id NOT IN (
				SELECT r.stage_id
				FROM stage_rules as r
			)
			AND s.process_id = " . $this->db->escape($process_id)
		);
		
		$stage_id = $first_stage_query->row_array();
		
		if (count($stage_id) != 0) {
			
			// Create new process instance
			
			$this->db->query("
				INSERT INTO process_instances
				VALUES (
					'',
					" . $this->db->escape($process_id) . ",
					" . $this->db->escape($user_id) . ",
					NOW(),
					NULL
				)
			");
		
			$process_instance_id = $this->db->insert_id();
			
			// Create new stage instance
			
			$stage_id = $stage_id['stage_id'];
			
			$this->db->query("
				INSERT INTO stage_instances
				VALUES (
					'',
					" . $this->db->escape($process_instance_id) . ",
					" . $this->db->escape($stage_id) . ",
					NULL,
					NOW(),
					NULL
				)
			");
			
			return true;
		}

		return false;
	}
	
	function get_processes_where_user_participates_in($user_id) {
		
		// Get all the process_ids from the database where the user is a stage participant
	
		$stages_query = $this->db->query("
			SELECT s.process_id, s.stage_id
			FROM stages as s, stage_participants as p
			WHERE s.stage_id = p.stage_id
			AND p.user_id = " . $this->db->escape($user_id) . "
		");
		
		$stages_result = $stages_query->result_array();
		
		if (count($stages_result) != 0) {
			$processes = array();
			$stages = array();
			
			foreach ($stages_result as $stage) {
				$processes[] = $stage['process_id'];
				$stages[] = $stage['stage_id'];
			}
			
			$processes = implode(',', $processes);
			
			// Get the processes where the user is a participant in
			
			$processes_query = $this->db->query("
				SELECT p.process_name, i.start_date, i.process_instance_id
				FROM processes as p, process_instances as i
				WHERE i.process_id = p.process_id
				AND p.process_id IN (" . $processes . ")
			");
			
			$processes_result = $processes_query->result_array();
			
			if (count($processes_result) != 0) {
				
				$process_instances = array();
				
				foreach ($processes_result as $process) {
					$process_instances[] = $process['process_instance_id'];
				}
				
				$process_instances = implode(',', $process_instances);
				
				// Get the current stages the process is in
				
				$current_stages_query = $this->db->query("
					SELECT i.start_date, i.process_instance_id, s.stage_name, i.stage_instance_id, s.stage_id
					FROM stage_instances as i, stages as s
					WHERE i.stage_id = s.stage_id
					AND i.end_date IS NULL
					AND i.process_instance_id IN (" . $process_instances . ")
				");
				
				$current_stages_result = $current_stages_query->result_array();
				
				// Model all the data in the wanted format
				
				if (count($current_stages_result) != 0) {
					
					$data = array();
					
					foreach ($processes_result as $p) {
						$pr = array(
							'process_name'	=> $p['process_name'],
							'start_date'	=> $p['start_date'],
							'stages'		=> array()
						);
						
						foreach ($current_stages_result as $csr) {
							if ($csr['process_instance_id'] == $p['process_instance_id']) {
								
								$pr['stages'][] = array(
									'stage_name'			=> $csr['stage_name'],
									'start_date'			=> $csr['start_date'],
									'process_instance_id'	=> $csr['process_instance_id'],
									'stage_instance_id'		=> $csr['stage_instance_id'],
									'participates'			=> (in_array($csr['stage_id'], $stages))
								);
							}
						}
						
						if (count($pr['stages']) != 0) {
							$data[] = $pr;
						}
					}
					
					return $data;
				}
			}
		}
		
		return NULL;
	}
	
	function get_user_participation($user_id, $stage_instance_id) {
		$query = $this->db->query("
			SELECT COUNT(p.user_id) as number
			FROM stage_participants as p, stage_instances as i
			WHERE p.stage_id = i.stage_id
			AND p.user_id = " . $this->db->escape($user_id) . "
			AND i.stage_instance_id = " . $this->db->escape($stage_instance_id) . "
		");
		
		$result = $query->row_array();
		
		return ($result['number'] != 0);
	}
	
	function get_stage_details($stage_instance_id) {
		$query = $this->db->query("
			SELECT s.stage_id, s.stage_name, s.stage_description
			FROM stages as s, stage_instances as i
			WHERE s.stage_id = i.stage_id
			AND i.stage_instance_id = " . $this->db->escape($stage_instance_id) . "
		");
		
		return $query->row_array();
	}
	
	function get_all_contributions($stage_instance_id, $stage_id) {
		
		$data = array();
		
		$contribution_query = $this->db->query("
			SELECT c.contribution_id, c.contribution_title
			FROM contributions as c
			WHERE c.stage_id = " . $this->db->escape($stage_id) . "
		");
		
		$contribution_result = $contribution_query->result_array();
		
		
		if (count($contribution_result) != 0) {
			$contribution_ids_array = array();
			
			foreach ($contribution_result as $contribution) {
				$contribution_ids_array[] = "'" . $contribution['contribution_id'] . "'";
			}
			
			$contribution_ids = implode(',', $contribution_ids_array);
			
			$instances_query = $this->db->query("
				SELECT i.contribution_id, i.added_date, u.firstname, u.surname
				FROM contribution_instances as i, users as u
				WHERE i.user_id = u.user_id
				AND i.contribution_id IN (" . $contribution_ids . ")
				AND i.stage_instance_id = " . $this->db->escape($stage_instance_id) . "
			");
			
			$instances_result = $instances_query->result_array();
			
			
			foreach ($contribution_result as $contribution) {
				$row = array(
					'contribution_id'	=> $contribution['contribution_id'],
					'contribution_name' => $contribution['contribution_title'],
					'status'			=> false
				);
				
				foreach ($instances_result as $instance) {
					if ($contribution['contribution_id'] == $instance['contribution_id']) {
						$row['status'] = true;
						$row['firstname'] = $instance['firstname'];
						$row['surname'] = $instance['surname'];
						$row['added_date'] = $instance['added_date'];
					}
				}
				
				$data[] = $row;
			}
		}
		
		return $data;
	}
	
	function get_contribution_details($contribution_id) {
		$query = $this->db->query("
			SELECT c.contribution_title, c.contribution_description
			FROM contributions as c
			WHERE c.contribution_id = " . $this->db->escape($contribution_id) . "
		");
		
		return $query->row_array();
	}
	
	function get_attributes($contribution_id) {
		$query = $this->db->query("
			SELECT a.attribute_id, a.attribute_name, a.attribute_description, a.attribute_type, a.optional
			FROM attributes as a
			WHERE a.contribution_id = " . $this->db->escape($contribution_id) . "
		");
		
		return $query->result_array();
	}
	
	function create_contribution_instance($contribution_id, $user_id, $stage_instance_id) {
		$this->db->query("
			INSERT INTO contribution_instances
			VALUES (
				'',
				" . $this->db->escape($contribution_id) . ",
				" . $this->db->escape($user_id) . ",
				NOW(),
				" . $this->db->escape($stage_instance_id) . "
			)
		");
		
		return $this->db->insert_id();
	}
	
	function set_attributes($contribution_instance_id, $attributes) {	
		foreach ($attributes as $att_id => $att_value) {
			$this->db->query("
				INSERT INTO input_datas
				VALUES (
					'',
					" . $this->db->escape($contribution_instance_id) . ",
					" . $this->db->escape($att_id) . ",
					" . $this->db->escape($att_value) . "
				)
			");
		}
	}
	
	function set_file($contribution_instance_id, $attribute_id, $file_name, $file_size, $file_content) {
		$this->db->query("
			INSERT INTO documents
			VALUES (
				'',
				" . $this->db->escape($contribution_instance_id) . ",
				" . $this->db->escape($attribute_id) . ",
				" . $this->db->escape($file_name) . ",
				" . $this->db->escape($file_size) . ",
				" . $this->db->escape($file_content) . "
			)
		");
	}
	
	function is_all_contributions_finished($stage_instance_id) {
		
		// Get the stage_id
		
		$stage_query = $this->db->query("
			SELECT i.stage_id
			FROM stage_instances as i
			WHERE i.stage_instance_id = " . $this->db->escape($stage_instance_id) . "
		");
		
		$stage_result = $stage_query->row_array();
		
		$stage_id = $stage_result['stage_id'];
		
		// Count the number of needed contributions
		
		$contr_query = $this->db->query("
			SELECT COUNT(c.contribution_id) as number_of_contributions
			FROM contributions as c
			WHERE c.stage_id = " . $this->db->escape($stage_id) . "
		");
		
		$contr_result = $contr_query->row_array();
		
		$number_of_needed_contributions = $contr_result['number_of_contributions'];
		
		// Count the number of done contributions
		
		$contr_inst_query = $this->db->query("
			SELECT COUNT(i.contribution_instance_id) as number_of_instances
			FROM contribution_instances as i
			WHERE i.stage_instance_id = " . $this->db->escape($stage_instance_id) . "
		");
		
		$contr_inst_result = $contr_inst_query->row_array();
		
		$number_of_done_contributions = $contr_inst_result['number_of_instances'];
		
		return $number_of_needed_contributions == $number_of_done_contributions;
	}
	
	function finish_stage($stage_instance_id, $user_id) {
		
		// Finish the stage
		
		$this->db->query("
			UPDATE stage_instances
			SET user_id = " . $this->db->escape($user_id) . ",
			end_date = NOW()
			WHERE stage_instance_id = " . $this->db->escape($stage_instance_id) . "
		");
		
		// Get process information
		
		$process_query = $this->db->query("
			SELECT p.process_instance_id, p.process_id, s.stage_id
			FROM process_instances as p, stage_instances as s
			WHERE s.process_instance_id = p.process_instance_id
			AND s.stage_instance_id = " . $this->db->escape($stage_instance_id) . "
		");
		
		$process_result = $process_query->row_array();
		
		$process_instance_id = $process_result['process_instance_id'];
		$process_id = $process_result['process_id'];
		$stage_id = $process_result['stage_id'];
		
		// Count the needed finished stages
		
		$needed_stages_query = $this->db->query("
			SELECT COUNT(s.stage_id) as stages
			FROM stages as s
			WHERE s.process_id = " . $this->db->escape($process_id) . "
		");
		
		$needed_stages_result = $needed_stages_query->row_array();
		
		$needed_stages = $needed_stages_result['stages'];
		
		// Count the already finished stages
		
		$finished_stages_query = $this->db->query("
			SELECT COUNT(i.stage_instance_id) as stages
			FROM stage_instances as i
			WHERE i.process_instance_id = " . $this->db->escape($process_instance_id) . "
			AND i.end_date IS NOT NULL
		");
		
		$finished_stages_result = $finished_stages_query->row_array();
		
		$finished_stages = $finished_stages_result['stages'];
		
		// Try to finish the process, else continue
		
		if ($needed_stages == $finished_stages) {
			$this->db->query("
				UPDATE process_instances
				SET end_date = NOW()
				WHERE process_instance_id = " . $this->db->escape($process_instance_id) . "
			");
		}
		else {
			
			$next_stages_query = $this->db->query("
				SELECT r.stage_id
				FROM stage_rules as r
				WHERE r.previous_stage_id = " . $this->db->escape($stage_id) . "
			");
			
			$next_stages_result = $next_stages_query->result_array();
			
			if (count($next_stages_result) != 0) {
				foreach ($next_stages_result as $next_stage) {
					$this->db->query("
						INSERT INTO stage_instances
						VALUES (
							'',
							" . $this->db->escape($process_instance_id) . ",
							" . $this->db->escape($next_stage['stage_id']) . ",
							NULL,
							NOW(),
							NULL
						)
					");
				}
			}
			
		}
	}
}

?>